package cn.buk.tms.dao;

import cn.buk.common.sc.Page;
import cn.buk.tms.sc.SearchCustomerCriteria;
import cn.buk.common.util.DateUtil;
import cn.buk.tms.entity.base.City;
import cn.buk.tms.entity.misc.*;
import jakarta.persistence.criteria.*;
import org.springframework.stereotype.Component;

import jakarta.transaction.Transactional;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static cn.buk.tms.common.constant.TmsOrderConstant.OPERATING_OBJECT_BASIC_DATA;
import static cn.buk.tms.common.constant.TmsOrderConstant.OPERATING_OBJECT_CUSTOMER_BASIC_DATA;

/**
 * @author yfdai
 */
@Component
@Transactional
public class CustomerDaoImpl extends AbstractDao implements CustomerDao {

  @Override
  public int create(Customer customer) {
    em.persist(customer);
    em.flush();

    return customer.getId();
  }

  @Override
  public int update(Customer customer) {
    int statusCode = 0;

    em.merge(customer);

    statusCode = customer.getId();
    return statusCode;
  }

  /* (non-Javadoc)
   * @see cn.buk.tms.dao.CustomerDao#delete(int)
   */
  @Override
  public int delete(int id) {
    int statusCode = 0;

    Customer customer = em.find(Customer.class, id);
    if (customer != null) {
      em.remove(customer);
      statusCode = id;
    }


    return statusCode;
  }

  @Override
  public Customer getCustomerById(int id) {
    return getCustomerById(id, false);
  }

  @Override
  public Customer getCustomerById(int id, boolean forceRefresh) {
    Customer customer = em.find(Customer.class, id);
    if (customer != null && forceRefresh) {
      em.refresh(customer);
    }

    return customer;
  }

  @Override
  public Customer getCustomerByCustomerEnterpriseId(int enterpriseId, int customerEnterpriseId) {
    String hql = "select o from Customer o where o.enterpriseId = :enterpriseId and o.customerEnterpriseId = :customerEnterpriseId";
    List<Customer> customers = em.createQuery(hql, Customer.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerEnterpriseId", customerEnterpriseId)
            .getResultList();

    return customers.size() == 1 ? customers.get(0) : null;
  }

  @Override
  public List<Customer> searchCustomer(SearchCustomerCriteria sc) {
    String hql = "select count(o)  from Customer o where o.enterpriseId = :enterpriseId";

    Long total = (Long) em.createQuery(hql).setParameter("enterpriseId", sc.getEnterpriseId()).getSingleResult();

    if (total == 0) {
      return new ArrayList<>();
    }

    Page page = sc.getPage();
    page.setRowCount(total.intValue());
    int i = (page.getPageNo() - 1) * page.getPageSize();

    hql = "select o from Customer o where o.enterpriseId = :enterpriseId order by o.vipName";
    List<Customer> customers = em.createQuery(hql, Customer.class)
            .setParameter("enterpriseId", sc.getEnterpriseId())
            .setFirstResult(i)
            .setMaxResults(page.getPageSize())
            .getResultList();
    return customers;
  }

  @Override
  public List<Customer> searchCustomerByCustomerName(int enterpriseId,
                                                     String customerName) {
    return em.createQuery("select o from Customer o where o.enterpriseId = :enterpriseId and o.vipName = :vipName", Customer.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("vipName", customerName)
            .getResultList();
  }

  /**
   * 查找所有企业客户信息
   *
   * @param enterpriseId 拥有者id
   * @return 企业客户信息列表
   */
  @Override
  public List<Customer> getAllCustomers(int enterpriseId) {
    return em.createQuery("select o from Customer o where o.enterpriseId = :enterpriseId order by o.vipName", Customer.class)
            .setParameter("enterpriseId", enterpriseId)
            .getResultList();
  }

  @Override
  public Map<Integer, Customer> getCustomerSets(int enterpriseId) {
    Map<Integer, Customer> map = new HashMap<>(10);
    String hql = "select o from Customer o where o.enterpriseId = :enterpriseId order by o.vipName";

    List<Customer> customers = em.createQuery(hql, Customer.class)
            .setParameter("enterpriseId", enterpriseId)
            .getResultList();

    for (Customer o : customers) {
      map.put(o.getId(), o);
    }

    return map;
  }

  /**
   * 查找客户信息
   *
   */
  @Override
  public List<Customer> searchCustomers(final int enterpriseId, SearchCustomerCriteria sc, List<Integer> allowedCustomerIds) {
    List<Customer> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Customer> cq = cb.createQuery(Customer.class);
    Root<Customer> root = cq.from(Customer.class);
    root.alias("o");

    var predicates0 = getSearchCustomersPredicates(cb, root, enterpriseId, sc, allowedCustomerIds);


    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<Customer> countRoot = countQuery.from(Customer.class);
    countRoot.alias("o");

    var predicates1 = getSearchCustomersPredicates(cb, countRoot, enterpriseId, sc, allowedCustomerIds);


    countQuery.select(cb.count(countRoot)).where(predicates1.toArray(new Predicate[predicates1.size()]));
    Long count = em.createQuery(countQuery).getSingleResult();

    if (count.intValue() > 0) {
      cq.where(predicates0.toArray(new Predicate[predicates0.size()]));
      List<Order> orderByes = new ArrayList<>();

      if (sc.getOrderBy() == 1) {
        // 协议截止日期 倒序
        orderByes.add(cb.desc(root.get("agreementEndDate")));
      } else if (sc.getOrderBy() == 3) {
        //最新购买日期 倒序
        orderByes.add(cb.desc(root.get("recency")));
      }

      orderByes.add(cb.desc(root.get("status")));
      orderByes.add(cb.asc(root.get("pinyin")));
      orderByes.add(cb.asc(root.get("vipName")));
      orderByes.add(cb.desc(root.get("id")));

      cq.orderBy(orderByes);


      Page page = sc.getPage();
      page.setRowCount(count.intValue());
      int i = (page.getPageNo() - 1) * page.getPageSize();

      if (i < 0) {
        i = 0;
      }

      results = em.createQuery(cq)
              .setFirstResult(i)
              .setMaxResults(page.getPageSize()).getResultList();
    }

    return results == null ? new ArrayList<>() : results;
  }

  private List<Predicate> getSearchCustomersPredicates(CriteriaBuilder cb, Root<Customer> root, final int enterpriseId,
                                                       SearchCustomerCriteria sc, List<Integer> allowedCustomerIds) {
    List<Predicate> predicates = new ArrayList<>();
    Predicate where = cb.conjunction();
    predicates.add(where);

    where = cb.equal(root.get(Customer_.enterpriseId), enterpriseId);
    predicates.add(where);

    if (sc.getName() != null && sc.getName().trim().length() > 0) {
      String temp = "%" + sc.getName().trim() + "%";
      where = cb.like(root.get(Customer_.customerCode), temp);
      where = cb.or(where, cb.like(root.get(Customer_.vipName), temp));
      where = cb.or(where, cb.like(root.get(Customer_.pinyin), temp));
      predicates.add(where);
    }

    if (sc.getCustomerId() > 0) {
      where = cb.equal(root.get(Customer_.id), sc.getCustomerId());
      predicates.add(where);
    }

    if (sc.getEnterpriseType() == 0) {
      where = cb.equal(root.get(Customer_.enterpriseType), 0);
      predicates.add(where);
    } else if (sc.getEnterpriseType() == 1) {
      where = cb.isNull(root.get(Customer_.enterpriseType));
      where = cb.or(where, cb.equal(root.get(Customer_.enterpriseType), 1));
      predicates.add(where);
    }

    if (sc.getStatus() > -1) {
      if (sc.getStatus() == 0) {
        // 停用状态
        where = cb.equal(root.get(Customer_.status), 0);
        predicates.add(where);
      } else if (sc.getStatus() == 1) {
        // 启用状态
        where = cb.isNull(root.get(Customer_.status));
        where = cb.or(where, cb.equal(root.get(Customer_.status), 1));
        predicates.add(where);
      }
    }

    // 协议截止日期
    if (sc.getEndDate() != null) {
      where = cb.lessThanOrEqualTo(root.get(Customer_.agreementEndDate), sc.getEndDate());
      predicates.add(where);
    }

    if (sc.getGrouped() == 1) {
      where = cb.isNotNull(root.get(Customer_.customerServiceGroupCode));
      predicates.add(where);
    } else if (sc.getGrouped() == 0) {
      where = cb.isNull(root.get(Customer_.customerServiceGroupCode));
      predicates.add(where);
    }

    if (allowedCustomerIds != null && !allowedCustomerIds.isEmpty()) {
      // 只允许查看指定的企业客户的订单
      CriteriaBuilder.In<Integer> predicate0 = cb.in(root.get(Customer_.id));

      for (Integer customerId : allowedCustomerIds) {
        predicate0 = predicate0.value(customerId);
      }

      predicates.add(predicate0);
    }

    return predicates;
  }

  @Override
  public List<Customer> searchCustomerByCustomerEnterpriseId(int enterpriseId, int customerEnterpriseId) {
    return em.createQuery("select o from Customer o where o.customerEnterpriseId = :customerEnterpriseId and o.enterpriseId = :enterpriseId order by o.id", Customer.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerEnterpriseId", customerEnterpriseId)
            .getResultList();
  }

  private List<Predicate> buildPredicates_searchCustomerTravelZones(CriteriaBuilder cb, Root<CustomerTravelZone> root, int enterpriseId, SearchCustomerCriteria sc) {
    List<Predicate> predicates = new ArrayList<>();

    Predicate predicate = cb.conjunction();
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTravelZone_.enterpriseId), enterpriseId);
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTravelZone_.customerId), sc.getCustomerId());
    predicates.add(predicate);

    return predicates;
  }

  @Override
  public List<CustomerTravelZone> searchCustomerTravelZones(int enterpriseId, SearchCustomerCriteria sc) {
    List<CustomerTravelZone> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<CustomerTravelZone> cq = cb.createQuery(CustomerTravelZone.class);
    Root<CustomerTravelZone> root = cq.from(CustomerTravelZone.class);
    root.alias("o");

    var predicates0 = buildPredicates_searchCustomerTravelZones(cb, root, enterpriseId, sc);

    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<CustomerTravelZone> countRoot = countQuery.from(CustomerTravelZone.class);
    countRoot.alias("o");

    var predicates1 = buildPredicates_searchCustomerTravelZones(cb, countRoot, enterpriseId, sc);

    // count items
    countQuery.select(cb.count(countRoot))
            .where(predicates1.toArray(new Predicate[predicates1.size()]));
    Long count = em.createQuery(countQuery).getSingleResult();



    //查询
    cq.select(root).where(predicates0.toArray(new Predicate[predicates0.size()]));

    List<Order> orderByes = new ArrayList<>();
    orderByes.add(cb.asc(root.get("travelZoneId")));
    orderByes.add(cb.asc(root.get("city")));

    cq.orderBy(orderByes);

    int maxResults = count.intValue();
    if (maxResults > 0) {
      Page page = sc.getPage();
      page.setRowCount(maxResults);

      try {
        results = em.createQuery(cq)
                .setFirstResult(page.getFirstPosition())
                .setMaxResults(page.getPageSize())
                .getResultList();

      } catch (Exception ex) {
        ex.printStackTrace();
      }
    }

    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public int removeCustomerTravelZone(int enterpriseId, String username, int customerId, int zoneId, int cityId) {
    int retCode = em.createQuery("delete from CustomerTravelZone o " +
            "where o.enterpriseId = :enterpriseId " +
            "and o.customer.id = :customerId " +
            "and o.travelZoneId = :zoneId " +
            "and o.city.id = :cityId")
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .setParameter("zoneId", zoneId)
            .setParameter("cityId", cityId)
            .executeUpdate();
    if (retCode > 0) {
      City city = em.find(City.class, cityId);
      OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
      log.setOperator(username);
      log.setContent("移除差旅地区(" + zoneId + ")的城市(" + city.getCityName() + ")");

      em.persist(log);
    }
    return retCode;
  }

  @Override
  public int addCustomerTravelZone(int enterpriseId, String username, int customerId, int zoneId, int cityId) {
    CustomerTravelZone o = new CustomerTravelZone();
    o.setEnterpriseId(enterpriseId);

    Customer c = em.find(Customer.class, customerId);
    o.setCustomer(c);

    o.setTravelZoneId(zoneId);

    City city = em.find(City.class, cityId);
    o.setCity(city);

    em.persist(o);

    OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
    log.setOperator(username);
    log.setContent("增加差旅地区(" + zoneId + ")的城市(" + city.getCityName() + ")");

    em.persist(log);

    return o.getId();
  }

  @Override
  public CustomerTravelZone getTravelZoneByCityId(int enterpriseId, int customerId, int cityId) {
    List<CustomerTravelZone> list = em.createQuery("select o from CustomerTravelZone o " +
            "where o.enterpriseId = :enterpriseId " +
            "and o.customer = :customerId " +
            "and o.city = :cityId", CustomerTravelZone.class)
            .getResultList();
    return list.size() == 1 ? list.get(0) : null;
  }

  @Override
  public CustomerPosition getCustomerPositionById(int id) {
    return em.find(CustomerPosition.class, id);
  }

  @Override
  public int saveCustomerTpHotel(int enterpriseId, String username, CustomerTpHotel tpHotel) {
    tpHotel.setEnterpriseId(enterpriseId);
    em.persist(tpHotel);

    OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
    log.setOperator(username);
    log.setContent("增加企业酒店差旅政策：" + tpHotel.toString());
    em.persist(log);

    return tpHotel.getId();
  }

  private List<Predicate> buildPredicates_searchCustomerTpHotels(CriteriaBuilder cb,
                                                               Root<CustomerTpHotel> root,
                                                               int enterpriseId, SearchCustomerCriteria sc) {
    List<Predicate> predicates = new ArrayList<>();

    Predicate predicate = cb.conjunction();
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTpHotel_.enterpriseId), enterpriseId);
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTpHotel_.customerId), sc.getCustomerId());
    predicates.add(predicate);

    return predicates;
  }

  @Override
  public List<CustomerTpHotel> searchCustomerTpHotels(int enterpriseId, SearchCustomerCriteria sc) {
    List<CustomerTpHotel> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<CustomerTpHotel> cq = cb.createQuery(CustomerTpHotel.class);
    Root<CustomerTpHotel> root = cq.from(CustomerTpHotel.class);
    root.alias("o");

    var predicates0 =  buildPredicates_searchCustomerTpHotels(cb, root, enterpriseId, sc);

    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<CustomerTpHotel> countRoot = countQuery.from(CustomerTpHotel.class);
    countRoot.alias("o");

    var predicates1 =  buildPredicates_searchCustomerTpHotels(cb, countRoot, enterpriseId, sc);

    // count items
    countQuery.select(cb.count(countRoot))
            .where(predicates1.toArray(new Predicate[predicates1.size()]));
    Long count = em.createQuery(countQuery).getSingleResult();

    //查询
    cq.select(root).where(predicates0.toArray(new Predicate[predicates0.size()]));

    List<Order> orderByes = new ArrayList<>();
    orderByes.add(cb.asc(root.get("customerPosition")));
    orderByes.add(cb.asc(root.get("zoneId")));

    cq.orderBy(orderByes);

    int maxResults = count.intValue();
    if (maxResults > 0) {
      Page page = sc.getPage();
      page.setRowCount(maxResults);

      try {
        results = em.createQuery(cq)
                .setFirstResult(page.getFirstPosition())
                .setMaxResults(page.getPageSize())
                .getResultList();

      } catch (Exception ex) {
        ex.printStackTrace();
      }
    }

    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public List<CustomerTpHotel> searchCustomerTpHotels(int enterpriseId, int customerId) {
    return em.createQuery("select o from CustomerTpHotel o " +
            "where o.enterpriseId = :enterpriseId " +
            "and o.customer.id = :customerId order by o.customerPosition.positionId desc", CustomerTpHotel.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .getResultList();
  }

  @Override
  public int deleteCustomerTpHotel(int enterpriseId, String username, int customerId, int id) {
    CustomerTpHotel tpHotel = em.find(CustomerTpHotel.class, id);

    int retCode = em.createQuery("delete from CustomerTpHotel o " +
            "where o.id = :id " +
            "and o.enterpriseId = :enterpriseId " +
            "and o.customer.id = :customerId")
            .setParameter("id", id)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .executeUpdate();
    if (retCode == 1) {
      OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
      log.setOperator(username);
      log.setContent("删除企业酒店差旅政策：" + tpHotel.toString());
      em.persist(log);
    }

    return retCode;
  }

  @Override
  public int saveCustomerTpFlight(int enterpriseId, String username, CustomerTpFlight tpFlight) {
    tpFlight.setEnterpriseId(enterpriseId);
    em.persist(tpFlight);

    OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
    log.setOperator(username);
    log.setContent("增加企业酒店差旅政策：" + tpFlight.toString());
    em.persist(log);

    return tpFlight.getId();
  }

  private List<Predicate> buildPredicates_searchCustomerTpFlights(CriteriaBuilder cb,Root<CustomerTpFlight> root,
                                                                  int enterpriseId, SearchCustomerCriteria sc) {
    List<Predicate> predicates = new ArrayList<>();

    Predicate predicate = cb.conjunction();
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTpFlight_.enterpriseId), enterpriseId);
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTpFlight_.customerId), sc.getCustomerId());
    predicates.add(predicate);

    return predicates;
  }

  @Override
  public List<CustomerTpFlight> searchCustomerTpFlights(int enterpriseId, SearchCustomerCriteria sc) {
    List<CustomerTpFlight> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<CustomerTpFlight> cq = cb.createQuery(CustomerTpFlight.class);
    Root<CustomerTpFlight> root = cq.from(CustomerTpFlight.class);
    root.alias("o");

    var predicates0 =  buildPredicates_searchCustomerTpFlights(cb, root, enterpriseId, sc);



    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<CustomerTpFlight> countRoot = countQuery.from(CustomerTpFlight.class);
    countRoot.alias("o");

    var predicates1 =  buildPredicates_searchCustomerTpFlights(cb, countRoot, enterpriseId, sc);

    // count items
    countQuery.select(cb.count(countRoot))
            .where(predicates1.toArray(new Predicate[predicates1.size()]));
    Long count = em.createQuery(countQuery).getSingleResult();

    //查询
    cq.select(root).where(predicates0.toArray(new Predicate[predicates0.size()]));

    List<Order> orderByes = new ArrayList<>();
    orderByes.add(cb.asc(root.get("customerPosition")));
//    orderByes.add(cb.asc(root.get("zoneId")));

    cq.orderBy(orderByes);

    int maxResults = count.intValue();
    if (maxResults > 0) {
      Page page = sc.getPage();
      page.setRowCount(maxResults);

      try {
        results = em.createQuery(cq)
                .setFirstResult(page.getFirstPosition())
                .setMaxResults(page.getPageSize())
                .getResultList();

      } catch (Exception ex) {
        ex.printStackTrace();
      }
    }

    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public List<CustomerTpFlight> searchCustomerTpFlights(int enterpriseId, int customerId) {
    return em.createQuery("select o from CustomerTpFlight o " +
            "where o.enterpriseId = :enterpriseId " +
            "and o.customer.id = :customerId order by o.customerPosition.positionId desc", CustomerTpFlight.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .getResultList();
  }

  @Override
  public int deleteCustomerTpFlight(int enterpriseId, String username, int customerId, int id) {
    CustomerTpFlight tp = em.find(CustomerTpFlight.class, id);

    int retCode = em.createQuery("delete from CustomerTpFlight o " +
            "where o.id = :id " +
            "and o.enterpriseId = :enterpriseId " +
            "and o.customer.id = :customerId")
            .setParameter("id", id)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .executeUpdate();
    if (retCode == 1) {
      OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
      log.setOperator(username);
      log.setContent("删除企业机票差旅政策：" + tp.toString());
      em.persist(log);
    }

    return retCode;
  }

  @Override
  public int saveCustomerTpCar(int enterpriseId, String username, CustomerTpCar tpCar) {
    tpCar.setEnterpriseId(enterpriseId);
    em.persist(tpCar);

    OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
    log.setOperator(username);
    log.setContent("增加企业用车差旅政策：" + tpCar.toString());
    em.persist(log);

    return tpCar.getId();
  }

  @Override
  public int deleteCustomerTpCar(int enterpriseId, String username, int customerId, int id) {
    CustomerTpCar tp = em.find(CustomerTpCar.class, id);

    int retCode = em.createQuery("delete from CustomerTpCar o " +
            "where o.id = :id " +
            "and o.enterpriseId = :enterpriseId " +
            "and o.customer.id = :customerId")
            .setParameter("id", id)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .executeUpdate();
    if (retCode == 1) {
      OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
      log.setOperator(username);
      log.setContent("删除企业用车差旅政策：" + tp.toString());
      em.persist(log);
    }

    return retCode;
  }

  private List<Predicate> buildPredicates_searchCustomerTpCars(CriteriaBuilder cb,
                                                               Root<CustomerTpCar> root,
                                                               int enterpriseId, SearchCustomerCriteria sc) {
    List<Predicate> predicates = new ArrayList<>();

    Predicate predicate = cb.conjunction();
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTpCar_.enterpriseId), enterpriseId);
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTpCar_.customerId), sc.getCustomerId());
    predicates.add(predicate);

    return predicates;
  }

  @Override
  public List<CustomerTpCar> searchCustomerTpCars(int enterpriseId, SearchCustomerCriteria sc) {
    List<CustomerTpCar> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<CustomerTpCar> cq = cb.createQuery(CustomerTpCar.class);
    Root<CustomerTpCar> root = cq.from(CustomerTpCar.class);
    root.alias("o");

    var predicates0 = buildPredicates_searchCustomerTpCars(cb, root, enterpriseId, sc);


    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<CustomerTpCar> countRoot = countQuery.from(CustomerTpCar.class);
    countRoot.alias("o");

    var predicates1 = buildPredicates_searchCustomerTpCars(cb, countRoot, enterpriseId, sc);

    // count items
    countQuery.select(cb.count(countRoot))
            .where(predicates1.toArray(new Predicate[predicates1.size()]));
    Long count = em.createQuery(countQuery).getSingleResult();



    //查询
    cq.select(root).where(predicates0.toArray(new Predicate[predicates0.size()]));

    List<Order> orderByes = new ArrayList<>();
    orderByes.add(cb.asc(root.get("customerPosition")));
//    orderByes.add(cb.asc(root.get("zoneId")));

    cq.orderBy(orderByes);

    int maxResults = count.intValue();
    if (maxResults > 0) {
      Page page = sc.getPage();
      page.setRowCount(maxResults);

      try {
        results = em.createQuery(cq)
                .setFirstResult(page.getFirstPosition())
                .setMaxResults(page.getPageSize())
                .getResultList();

      } catch (Exception ex) {
        ex.printStackTrace();
      }
    }

    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public List<CustomerTpCar> searchCustomerTpCars(int enterpriseId, int customerId) {
    return em.createQuery("select o from CustomerTpCar o " +
            "where o.enterpriseId = :enterpriseId " +
            "and o.customer.id = :customerId order by o.customerPosition.positionId desc", CustomerTpCar.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .getResultList();
  }

  @Override
  public int saveCustomerTpTrain(int enterpriseId, String username, CustomerTpTrain tpTrain) {
    tpTrain.setEnterpriseId(enterpriseId);
    em.persist(tpTrain);

    OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
    log.setOperator(username);
    log.setContent("增加企业火车票差旅政策：" + tpTrain.toString());
    em.persist(log);

    return tpTrain.getId();
  }

  @Override
  public int deleteCustomerTpTrain(int enterpriseId, String username, int customerId, int id) {
    CustomerTpTrain tp = em.find(CustomerTpTrain.class, id);

    int retCode = em.createQuery("delete from CustomerTpTrain o " +
            "where o.id = :id " +
            "and o.enterpriseId = :enterpriseId " +
            "and o.customer.id = :customerId")
            .setParameter("id", id)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .executeUpdate();
    if (retCode == 1) {
      OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
      log.setOperator(username);
      log.setContent("删除企业火车票差旅政策：" + tp.toString());
      em.persist(log);
    }

    return retCode;
  }

  private List<Predicate> buildPredicates_searchCustomerTpTrains(CriteriaBuilder cb,
                                                               Root<CustomerTpTrain> root,
                                                               int enterpriseId, SearchCustomerCriteria sc) {
    List<Predicate> predicates = new ArrayList<>();

    Predicate predicate = cb.conjunction();
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTpTrain_.enterpriseId), enterpriseId);
    predicates.add(predicate);

    predicate = cb.equal(root.get(CustomerTpTrain_.customerId), sc.getCustomerId());
    predicates.add(predicate);

    return predicates;

  }

  @Override
  public List<CustomerTpTrain> searchCustomerTpTrains(int enterpriseId, SearchCustomerCriteria sc) {
    List<CustomerTpTrain> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<CustomerTpTrain> cq = cb.createQuery(CustomerTpTrain.class);
    Root<CustomerTpTrain> root = cq.from(CustomerTpTrain.class);
    root.alias("o");

    var predicates0 = buildPredicates_searchCustomerTpTrains(cb, root, enterpriseId, sc);

    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<CustomerTpTrain> countRoot = countQuery.from(CustomerTpTrain.class);
    countRoot.alias("o");

    var predicates1 = buildPredicates_searchCustomerTpTrains(cb, countRoot, enterpriseId, sc);

    // count items
    countQuery.select(cb.count(countRoot))
            .where(predicates1.toArray(new Predicate[predicates1.size()]));
    Long count = em.createQuery(countQuery).getSingleResult();

    //查询
    cq.select(root).where(predicates0.toArray(new Predicate[predicates0.size()]));

    List<Order> orderByes = new ArrayList<>();
    orderByes.add(cb.asc(root.get("customerPosition")));
//    orderByes.add(cb.asc(root.get("zoneId")));

    cq.orderBy(orderByes);

    int maxResults = count.intValue();
    if (maxResults > 0) {
      Page page = sc.getPage();
      page.setRowCount(maxResults);

      try {
        results = em.createQuery(cq)
                .setFirstResult(page.getFirstPosition())
                .setMaxResults(page.getPageSize())
                .getResultList();

      } catch (Exception ex) {
        ex.printStackTrace();
      }
    }

    if (results == null) {
      results = new ArrayList<>();
    }

    return results;
  }

  @Override
  public List<User> searchCustomerLoginInfos(final int ownerId, SearchCustomerCriteria sc) {
    List<User> results = null;

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> root = cq.from(User.class);
    root.alias("o");

    var predicates0 = getSearchCustomerLoginInfosPredicates(cb, root, ownerId, sc);

    //计算根据条件查询得出的数据总数
    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    Root<User> countRoot = countQuery.from(User.class);
    countRoot.alias("o");

    var predicates1 = getSearchCustomerLoginInfosPredicates(cb, countRoot, ownerId, sc);

    countQuery.select(cb.count(countRoot)).where(predicates1.toArray(new Predicate[predicates1.size()]));
    Long count = em.createQuery(countQuery).getSingleResult();

    if (count.intValue() > 0) {
      cq.where(predicates0.toArray(new Predicate[predicates0.size()]));
      List<Order> orderByes = new ArrayList<>();
      orderByes.add(cb.asc(root.get("status")));
      orderByes.add(cb.asc(root.get("username")));

      cq.orderBy(orderByes);

      Page page = sc.getPage();
      page.setRowCount(count.intValue());
      int i = (page.getPageNo() - 1) * page.getPageSize();

      if (i < 0) {
        i = 0;
      }

      results = em.createQuery(cq)
              .setFirstResult(i)
              .setMaxResults(page.getPageSize()).getResultList();
    }

    return results == null ? new ArrayList<>() : results;
  }

  private List<Predicate> getSearchCustomerLoginInfosPredicates(CriteriaBuilder cb,
                                                                Root<User> root,
                                                                final int ownerId,
                                                                SearchCustomerCriteria sc) {
    List<Predicate> predicates = new ArrayList<>();
    Predicate where = cb.conjunction();
    predicates.add(where);

    where = cb.equal(root.get(User_.parentEnterprise), ownerId);
    predicates.add(where);

    Customer c = em.find(Customer.class, sc.getCustomerId());
    where = cb.equal(root.get(User_.enterpriseId), c.getCustomerEnterpriseId());
    predicates.add(where);

    if (sc.getPassengerId() > 0) {
      where = cb.equal(root.get(User_.passengerId), sc.getPassengerId());
      predicates.add(where);
    }

    if (sc.getUsername() != null && sc.getUsername().trim().length() > 0) {
      where = cb.like(root.get(User_.username), "%" + sc.getUsername() + "%");
      predicates.add(where);
    }

    if (sc.getMobile() != null && sc.getMobile().trim().length() > 0) {
      where = cb.like(root.get(User_.mobileNo), "%" + sc.getMobile() + "%");
      predicates.add(where);
    }

    if (sc.getType() == User.USER_TYPE_PSG || sc.getType() == User.USER_TYPE_ENT) {
      where = cb.equal(root.get(User_.userType), sc.getType());
      predicates.add(where);
    }

    if (sc.getEtermUsername() != null && sc.getEtermUsername().trim().length() > 0) {
      where = cb.like(root.get(User_.etermUsername), '%' + sc.getEtermUsername().trim().toUpperCase() + '%');
      predicates.add(where);
    }

    if (sc.getStatus() >= 0) {
      where = cb.equal(root.get(User_.status), sc.getStatus());
      if (sc.getStatus() == 1) {
        where = cb.or(where, cb.isNull(root.get(User_.status)));
      }
      predicates.add(where);
    }

    return predicates;
  }

  @Override
  public int saveCustomerDepartmentApprover(final int enterpriseId, final int userId, final int customerId,
                                            final int departmentId, final int approverId, final int roleType) {
    int retCode;

    User user = em.find(User.class, userId);
    if (user.getEnterpriseId() != enterpriseId) {
      throw new RuntimeException("Data is not consistent.");
    }

    Customer c = em.find(Customer.class, customerId);
    if (c == null || c.getEnterpriseId() != enterpriseId) {
      throw new RuntimeException("Customer is not existed.");
    }

    CustomerDepartment department = em.find(CustomerDepartment.class, departmentId);
    if (department == null || department.getCustomer().getId() != customerId) {
      throw new RuntimeException("Department is not existed.");
    }

    User approver = em.find(User.class, approverId);
    if (approver == null || approver.getEnterpriseId() != c.getCustomerEnterpriseId()) {
      throw new RuntimeException("Approver is not existed.");
    }

    CustomerTravelApprover ta = new CustomerTravelApprover();
    ta.setEnterpriseId(enterpriseId);
    ta.setCustomer(c);
    ta.setDepartment(department);
    ta.setUser(approver);
    ta.setRoleType(roleType);

    em.persist(ta);

    OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
    log.setOperator(user.getUsername());
    String content = "设" + approver.getUsername()
            + "("
            + approver.getEmpname() + ","
            + department.getName() + ","
            + c.getVipName()
            + ")为 ";
    if (roleType == RoleInfo.ROLE_MANAGER_ID) {
      content += "经理 .";
    } else if (roleType == RoleInfo.ROLE_APPROVER_ID) {
      content += "差旅审批人 .";
    } else {
      content += roleType + " .";
    }
    log.setContent(content);
    em.persist(log);


    return ta.getId();
  }

  @Override
  public List<CustomerTravelApprover> searchCustomerApprovers(int enterpriseId, int customerId, int departmentId) {
    return em.createQuery("select o from CustomerTravelApprover o " +
            "where o.enterpriseId = : enterpriseId " +
            "and o.customer.id = :customerId " +
            "and o.department.id = :departmentId", CustomerTravelApprover.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .setParameter("departmentId", departmentId)
            .getResultList();

  }

  @Override
  public int deleteCustomerDepartmentApprover(int userId, int approverId) {
    int retCode;

    User user = em.find(User.class, userId);
    final int enterpriseId = user.getEnterpriseId();

    CustomerTravelApprover approver = em.find(CustomerTravelApprover.class, approverId);
    if (approver == null || approver.getUser().getParentEnterprise() != enterpriseId) {
      throw new RuntimeException("Approver is not existed.");
    }

    em.remove(approver);


    OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
    log.setOperator(user.getUsername());
    log.setContent("将" + approver.getUser().getUsername()
            + "("
            + approver.getUser().getEmpname() + ","
            + approver.getCustomer().getVipName() + ","
            + approver.getDepartment().getName()
            + ")从部门差旅审批人移除");
    em.persist(log);


    return approver.getId();
  }

  @Override
  public int disableCustomer(final int enterpriseId, final int id, final String username, final String remark) {
    int retCode = em.createQuery("update Customer o " +
            "set o.status = 0, o.lastUpdate = :lastUpdate " +
            "where " +
            "o.id = :id " +
            "and o.enterpriseId = :enterpriseId " +
            "and (o.status = 1 or o.status is null)")
            .setParameter("id", id)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("lastUpdate", DateUtil.getCurDateTime())
            .executeUpdate();
    if (retCode != 1) {
      throw new RuntimeException("Failed to disable customer.");
    }

    OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_BASIC_DATA);
    log.setOperator(username);
    log.setContent(remark);

    em.persist(log);

    return log.getId();
  }

  @Override
  public int saveCustomerSmsMode(final int enterpriseId, final int userId, final int customerId, final int smsMode) {
    int retCode = em.createQuery("update Customer o " +
            "set o.smsMode = :smsMode, o.lastUpdate = :lastUpdate " +
            "where " +
            "o.id = :id " +
            "and o.enterpriseId = :enterpriseId ")
            .setParameter("id", customerId)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("smsMode", smsMode)
            .setParameter("lastUpdate", DateUtil.getCurDateTime())
            .executeUpdate();
    if (retCode != 1) {
      throw new RuntimeException("Failed to save customer's sms_mode.");
    }

    User user = em.find(User.class, userId);
    OperationLog log = OperationLog.createLog(enterpriseId, OPERATING_OBJECT_CUSTOMER_BASIC_DATA);
    log.setOperator(user.getUsername());
    final String remark = "修改短信模式为 " + (smsMode == 1 ? "自动" : "手工");
    log.setContent(remark);

    em.persist(log);

    return log.getId();
  }

  @Override
  public List<CustomerAddress> searchCustomerAddresses(int enterpriseId, int customerId) {
    return em.createQuery("select o from CustomerAddress o " +
            "where o.enterpriseId = :enterpriseId " +
            "and o.customer.id = :customerId " +
            "order by o.alias", CustomerAddress.class)
            .setParameter("enterpriseId", enterpriseId)
            .setParameter("customerId", customerId)
            .getResultList();
  }

}
